package com.yyys.web.repository;

import com.yyys.web.entity.DRGoodsInfo;
import com.yyys.web.json.DRGoodsJson;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.List;

/**
 * Created by zhengsenwen on 2017/8/11.
 */

@Repository
public interface DRGoodsRepository extends JpaRepository<DRGoodsInfo, Serializable> {

//    @Override
//    List<DRGoodsInfo> findAll();


    List<DRGoodsInfo> findByspFlavor(String spFlavor);


//    SELECT [yyysweb].[dbo].[FLOW_DRGoodsInfo].* ,[yyysweb].[dbo].[Flow_CBInfo].*
//    FROM [yyysweb].[dbo].[FLOW_DRGoodsInfo], [yyysweb].[dbo].[Flow_CBInfo]
//    WHERE [yyysweb].[dbo].[FLOW_DRGoodsInfo].CBID = [yyysweb].[dbo].[Flow_CBInfo].id
//    AND [yyysweb].[dbo].[FLOW_DRGoodsInfo].RQ = '2017-02-21'
//    AND [yyysweb].[dbo].[FLOW_DRGoodsInfo].DRID = 1
//    AND [yyysweb].[dbo].[FLOW_DRGoodsInfo].SPClassName = '副食'
//
//    AND [yyysweb].[dbo].[Flow_CBInfo].CBName= '午餐'


    @Query(value = "SELECT DISTINCT d.spClassName FROM DRGoodsInfo d,CBInfo c  WHERE d.cbId = c.id " +
            "AND  d.drId = ?1 AND  c.cbName = ?2 AND d.rq = ?3")
    List<String> findSpClaseNameByDrId(Integer drId, String cbName, String rq);

//    @Query(value = "SELECT d , c FROM DRGoodsInfo d, CBInfo c WHERE d.cbId = c.id  " +
//            "AND d.rq=?1  AND d.drId = ?2 AND d.spClassName = ?3 AND c.cbName = ?4")
//    List<Object[]> findByDateDRClassAndCBName(String rq,Integer drId,String spClassName,String cbName);

    // TODO: 2017/8/12 修改后
    @Query(value = "SELECT d.drId,d.rq,d.spId,d.spName,d.spClassName,d.spPrice,d.spPic,d.spFlavor,d.sprIndex," +
            "d.cbId,d.mrtj,c.id,c.cbbh,c.cbName,c.bngTime,c.endTime FROM DRGoodsInfo d, CBInfo c WHERE d.cbId = c.id  " +
            "AND d.rq=?1  AND d.drId = ?2 AND d.spClassName = ?3 AND c.cbName = ?4")
    List<Object[]> findByDateDRClassAndCBName(String rq, Integer drId, String spClassName, String cbName);


    //    SELECT [yyysweb].[dbo].[FLOW_DRGoodsInfo].*
//    FROM [yyysweb].[dbo].[FLOW_DRGoodsInfo]
//    where [yyysweb].[dbo].[FLOW_DRGoodsInfo].RQ = '2017-08-28'
//    AND [yyysweb].[dbo].[FLOW_DRGoodsInfo].DRID = 1
//    AND [yyysweb].[dbo].[FLOW_DRGoodsInfo].mrtj = 1
    @Query(value = "SELECT d.drId,d.rq,d.spId,d.spName,d.spClassName,d.spPrice,d.spPic,d.spFlavor," +
            "d.sprIndex,d.cbId,d.mrtj FROM DRGoodsInfo d WHERE d.rq=?1  AND d.drId = ?2 AND d.mrtj = ?3")
    List<Object[]> findDailyRecommendByRQRAndDRID(String rq, Integer drId, String mrtj);


//    SELECT [yyysweb].[dbo].[FLOW_DRGoodsInfo].* ,[yyysweb].[dbo].[Flow_CBInfo].*
//    FROM [yyysweb].[dbo].[FLOW_DRGoodsInfo], [yyysweb].[dbo].[Flow_CBInfo]
//    WHERE [yyysweb].[dbo].[FLOW_DRGoodsInfo].CBID = [yyysweb].[dbo].[Flow_CBInfo].id
//    AND [yyysweb].[dbo].[FLOW_DRGoodsInfo].RQ = '2017-02-21'
//    AND [yyysweb].[dbo].[FLOW_DRGoodsInfo].DRID = 1
//    AND [yyysweb].[dbo].[FLOW_DRGoodsInfo].SPId = '20170219000303'

    @Query(value = "SELECT d.drId,d.rq,d.spId,d.spName,d.spClassName,d.spPrice,d.spPic,d.spFlavor,d.sprIndex," +
            "d.cbId,d.mrtj,c.id,c.cbbh,c.cbName,c.bngTime,c.endTime FROM DRGoodsInfo d, CBInfo c WHERE d.cbId = c.id  " +
            "AND d.rq=?1  AND d.drId = ?2 AND d.spId = ?3")
    List<Object[]> findGoodByDrIdSpIdAndCbId(String rq, Integer drId, String spId);

}